var ALL_SQL = {};

ALL_SQL.COUPON_ORDER = "SELECT u.id user_id,u.name,o.order_source,c.order_no,DATE(o.`SERVICE_REAL_END_TIME`) day,cp.rule_id,c.number,\
    (SELECT count(*) FROM tb_order oo \
        WHERE oo.`SERVICE_REAL_END_TIME` < o.`SERVICE_REAL_END_TIME` \
        AND oo.`STATUS`='3000' and oo.`USER_ID`=o.`USER_ID`) old_order_count\
 FROM `tb_order_coupon` c \
    LEFT JOIN tb_coupon cp ON c.`NUMBER`=cp.`NUMBER`\
    LEFT JOIN `tb_user` u ON u.id=cp.`USER`\
    LEFT JOIN tb_order o ON o.`ORDER_NO` = c.`ORDER_NO`\
    WHERE 1=1\
    AND o.`STATUS`='3000'\
    AND DATE(o.`SERVICE_REAL_END_TIME`) > '2015-07-01'\
    AND cp.`STATUS`='USED'\
    ORDER BY c.create_time DESC";

ALL_SQL.COUPON_STATISTICS = "select rule.id '规则ID',@etime '日期'\
        ,(select count(1) from tb_coupon_trace t,tb_coupon c WHERE t.COUPON_NUMBER = c.NUMBER AND t.result = 'SUCCESS' and t.COUPON_ACTION = 'BIND' AND date(t.trace_dt) = @etime and c.RULE_ID=rule.id ) '当天领取量'\
        ,sum(ifnull(o.count,0)) '当天使用量'\
        ,(select count(1) from tb_coupon where status in ('OWN','USED','LOCKED','EXPIRED') and rule_id=rule.id and date(own_dt)<=@etime) '累积领取'\
        ,(select count(1) from tr_coupon_order where rule_id=rule.id and @etime>=date(service_end_time)) '累积使用量'\
        ,(select count(1) from tr_coupon_order where rule_id=rule.id and date(service_end_time)=@etime and prevous_order_count=0) '当天新用户'\
        ,(select count(1) from tr_coupon_order where rule_id=rule.id and prevous_order_count=0 and @etime>=date(service_end_time)) '累积新用户'\
        ,(select count(distinct user_id) from tr_coupon_order where rule_id=rule.id and date(service_end_time)=@etime) '当天用户数'\
    from tb_coupon_rule rule left join (select *,1 count from tr_coupon_order where date(service_end_time)=@etime) o on rule.id = o.rule_id,\
        (select @etime :=?) as it\
        group by rule.id,date(o.service_end_time)\
        order by rule.id desc,date(o.service_end_time);"

ALL_SQL.PERSONAL_ORDER="select o.CITY,o.LAND_MARK,o.LONGITUDE,o.LATITUDE,date(o.CREATE_TIME) date\
        from tb_order o \
        where status=3000\
        and LONGITUDE is not null\
        and CITY=?\
        and MONTH(CREATE_TIME) BETWEEN ? AND ?";

module.exports = ALL_SQL;